﻿using Net.Sz.Framework.Szlog;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Text;


namespace Net.Sz.Framework.DB
{
    /// <summary>
    /// 
    /// <para>PS:</para>
    /// <para>@author 失足程序员</para>
    /// <para>@Blog http://www.cnblogs.com/shizuchengxuyuan/</para>
    /// <para>@mail 492794628@qq.com</para>
    /// <para>@phone 13882122019</para>
    /// </summary>
    public class SqliteImpl : Dal
    {
        private static SzLogger log = SzLogger.getLogger();
        private static String ifexitstable = "select sum(1) usm from sqlite_master where type ='table' and `name`= ? ;";

        public SqliteImpl(String dbUrl, bool showSql)
            : this(dbUrl, null, null, showSql)
        {

        }

        public SqliteImpl(String dbUrl, String dbUser, String dbPwd, bool showSql)
            : base(dbUrl, null, null, dbUser, dbPwd, showSql)
        {

        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public override DbConnection GetConnection()
        {
            SQLiteConnection con = new SQLiteConnection("Data Source=" + this.GetDbUrl() + ";Pooling=true;FailIfMissing=false", true);
            con.Open();
            return con;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="con"></param>
        /// <param name="tableName"></param>
        /// <param name="dbCache"></param>
        /// <returns></returns>
        protected override bool ExistsTable(DbConnection con, string tableName, DBCache dbCache)
        {
            if (this.ExecuteScalar<Int32>(con, ifexitstable, tableName) > 0)
            {
                if (this.IsShowSql())
                {
                    log.Error("表：" + tableName + " 检查结果：已存在");
                }
                if (dbCache != null && dbCache.Columns.Count > 0)
                {
                    foreach (var item in dbCache.Columns)
                    {
                        if (ExistsColumn(con, tableName, item.ColumnName))
                        {
                            if (this.IsShowSql())
                            {
                                log.Error("数据库：" + this.GetDbUrl() + " 表：" + tableName + " 映射数据库字段：" + item.ColumnName + " 检查结果：已存在，将不会修改");
                            }
                        }
                        else
                        {
                            throw new Exception("数据库：" + this.GetDbUrl() + " 表：" + tableName + " 字段：" + item.ColumnName + " 检查结果：无此字段 ");
                        }
                    }
                }
                return true;
            }
            log.Error("表：" + tableName + " 检查结果：无此表");
            return false;
        }

        protected override bool ExistsColumn(DbConnection con, string tableName, string columnName)
        {
            String ifexitscolumn = "SELECT sum(1) usm FROM sqlite_master WHERE name='" + tableName + "' AND sql like '%`" + columnName + "`%'";
            if (this.ExecuteScalar<Int32>(con, ifexitscolumn) > 0) { return true; }
            else { return false; }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="con"></param>
        /// <param name="cache"></param>
        protected override void CreateTable(DbConnection con, DBCache cache)
        {
            String sqls = null;
            if (ExistsTable(con, cache.TableName, null))
            {
                foreach (var column in cache.Columns)
                {
                    if (ExistsColumn(con, cache.TableName, column.ColumnName))
                    {
                        if (this.IsShowSql())
                        {
                            log.Error("表：" + cache.TableName + " 字段：" + column.Name + " 映射数据库字段：" + column.ColumnName + " 存在，将不会修改，");
                        }
                        /*   String sqls = "ALTER TABLE " + tableName + " CHANGE `" + key + "` " + value.getValue() + ";";
                                    if (showSql) {
                                        log.error("执行语句：" + sqls);
                                    }
                                    try (Statement cs1 = con.createStatement()) {
                                        boolean execute1 = cs1.execute(sqls);
                                        if (showSql) {
                                            log.error("执行结果：" + execute1);
                                        }
                                    }*/
                    }
                    else
                    {
                        sqls = "ALTER TABLE `" + cache.TableName + "` ADD `" + column.ColumnName + "` " + column.DBType + ";";
                        int execute1 = this.ExecuteQuery(con, sqls);
                        if (column.IsUnique)
                        {
                            sqls = "CREATE Unique INDEX un_key_" + cache.TableName + "_" + column.ColumnName + " ON " + cache.TableName + " (`" + column.ColumnName + "`);";
                            this.ExecuteQuery(con, sqls);
                        }
                        else if (column.IsIndex)
                        {
                            sqls = "CREATE INDEX in_key_" + cache.TableName + "_" + column.ColumnName + " ON " + cache.TableName + " (`" + column.ColumnName + "`);";
                            this.ExecuteQuery(con, sqls);
                        }
                    }
                }
            }
            else
            {
                StringBuilder sb = new StringBuilder();

                sb.AppendLine().Append("CREATE TABLE  if not exists ").Append(cache.TableName).AppendLine(" (");

                for (int i = 0; i < cache.Columns.Count; i++)
                {
                    var sqlColumn = cache.Columns[i];
                    if (!sqlColumn.IsTemp)
                    {
                        sb.Append("    `").Append(sqlColumn.ColumnName).Append("` ");
                        sb.Append(sqlColumn.DBType);
                        sb.AppendLine(",");
                    }
                }

                sb.Append("     PRIMARY KEY (");

                int pi = 0;
                for (int i = 0; i < cache.Columns.Count; i++)
                {
                    var sqlColumn = cache.Columns[i];
                    if (sqlColumn.IsP)
                    {
                        if (pi > 0)
                        {
                            sb.Append(", ");
                        }
                        sb.Append("`").Append(sqlColumn.ColumnName).Append("`");
                        pi++;
                    }
                }

                sb.AppendLine(")");
                sb.AppendLine(");");

                sqls = sb.ToString();
                if (this.IsShowSql())
                {
                    log.Error(sqls);
                }
                this.ExecuteQuery(con, sqls);
                if (this.IsShowSql())
                {
                    log.Error("创建表：" + cache.TableName + " 完成");
                }
                foreach (var column in cache.Columns)
                {
                    if (column.IsUnique)
                    {
                        sqls = "CREATE Unique INDEX un_key_" + cache.TableName + "_" + column.ColumnName + " ON " + cache.TableName + " (`" + column.ColumnName + "`);";
                        this.ExecuteQuery(con, sqls);
                    }
                    else if (column.IsIndex)
                    {
                        sqls = "CREATE INDEX in_key_" + cache.TableName + "_" + column.ColumnName + " ON " + cache.TableName + " (`" + column.ColumnName + "`);";
                        this.ExecuteQuery(con, sqls);
                    }
                }
            }
        }

        public override int CreateDatabase(string database)
        {
            throw new Exception("sqlite 无法创建数据库");
        }

        public override int DropDatabase(string database)
        {
            throw new Exception("sqlite 无法删除数据库");
        }

        protected override void SetStmtParam(DbParameterCollection stmt, object value)
        {
            ((SQLiteParameterCollection)stmt).AddWithValue(string.Empty, value);
        }

    }
}
